package name.luojun.DBhelper;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import name.luojun.DBhelper.model.Column;
import name.luojun.DBhelper.model.TableVO;

import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
public class TableManager extends NamedParameterJdbcDaoSupport {
	private Logger log = Logger.getLogger(TableManager.class);
	private static String classpath = Thread.currentThread().getContextClassLoader().getResource("").getPath();
	private String configPath[];
	private static String dialect = "oracle";
	private SAXReader saxreader = new SAXReader();
	public static Map<String, TableVO> tableMap = new LinkedHashMap<String, TableVO>();
	public String getDialect() {
		return dialect;
	}
	public void setDialect(String dialect) {
		this.dialect = dialect;
	}
	public String[] getConfigPath() {
		return configPath;
	}
	public void setConfigPath(String[] configPath) {
		this.configPath = configPath;
	}
	public static String cleanPath(String source) {
		int prefix = source.indexOf(":");
		if (prefix != -1) {
			String path = source.substring(prefix + 1);
			String pre = source.substring(0, prefix);
			if (pre.contains("classpath")) {
				String resolvepath = urlDecode(classpath + path);
				return resolvepath;
			} else {
				String resolvepath = urlDecode(source);
				return resolvepath;
			}
		} else {
			String resolvepath = urlDecode(source);
			return resolvepath;
		}
	}
	public static String urlDecode(String source) {
		StringBuffer result = new StringBuffer();
		boolean flag = false;
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		try {
			for (int i = 0; i < source.length(); i++) {
				char temp = source.charAt(i);
				if (temp == '%') {
					StringBuffer sb = new StringBuffer();
					sb.append(source.charAt(i + 1));
					sb.append(source.charAt(i + 2));
					int a = Integer.decode("0x" + sb.toString());
					bos.write(a);
					flag = true;
					i += 2;
					continue;
				} else {
					if (flag) {
						result.append(bos.toString("UTF-8"));
						bos.reset();
					}
					result.append(temp);
					flag = false;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.toString().replaceAll("//", "/").replaceAll("\\\\", "/");
	}
	@Override
	protected void checkDaoConfig() {
		super.checkDaoConfig();
		ResourcePatternResolver resourceLoader = new PathMatchingResourcePatternResolver();
		for (String path : configPath) {
			try {
				Resource[] source = resourceLoader.getResources(path);
				for (Resource r : source) {
					updateSchema(r);
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}
	@SuppressWarnings("unchecked")
	public void updateSchema(Resource source) throws DocumentException, IOException {
		Document document = saxreader.read(source.getInputStream());
		Element root = document.getRootElement();
		List<Element> elements = root.elements("table");
		for (Element e : elements) {
			TableVO table = parse(e);
			AlterTable(table);
			tableMap.put(table.getName(), table);
		}
		List<Element> sequences = root.elements("sequence");
		for (Element e : sequences) {
			String sequencename = e.attributeValue("name");
			try {
				AlterSeq(sequencename);
			} catch (Exception e1) {
				throw new RuntimeException(e1);
			}
		}
	}
	@SuppressWarnings("unchecked")
	public TableVO parse(Element e) {
		TableVO table = new TableVO();
		Map<String, Column> cols = new LinkedHashMap<String, Column>();
		List<String> pklist = new ArrayList<String>();
		Element pk = e.element("id");
		String tab = e.attributeValue("name");
		String cls = e.attributeValue("class");
		String mut = e.attributeValue("mutable");
		table.setName(tab.toUpperCase());
		table.setMutable(mut.equalsIgnoreCase("true") ? true : false);
		table.setClassname(cls);
		if (pk != null) {
			String pkname = pk.attributeValue("name");
			if (pkname != null) {
				table.setPkname(pkname.toUpperCase());
			}
			List<Element> pkcs = pk.elements("column");
			for (Element col : pkcs) {
				Column column = this.getColumn(col, cls);
				cols.put(column.getName(), column);
				pklist.add(column.getName());
			}
		}
		List<Element> pkcs = e.elements("column");
		for (Element col : pkcs) {
			Column column = this.getColumn(col, cls);
			cols.put(column.getName(), column);
		}
		table.setColumns(cols);
		table.setPk(pklist);
		return table;
	}
	public Column getColumn(Element e, String clazz) {
		String name = e.attributeValue("name");
		String dbtype = e.attributeValue("dbtype");
		String type = e.attributeValue("type");
		String lenstr = e.attributeValue("length");
		String notnull = e.attributeValue("not-null");
		if (notnull == null) {
			notnull = "false";
		}
		String defaultV = e.attributeValue("default");
		String field = e.attributeValue("field");
		String generator = e.attributeValue("generator");
		String sequence = e.attributeValue("sequence");
		if (name == null) {
			name = field;
		}
		if (field == null) {
			field = name;
		}
		if (type == null) {
			try {
				Field f = Class.forName(clazz).getDeclaredField(field);
				type = f.getType().getSimpleName();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		if (dbtype == null) {
			dbtype = SqlType.getSqlType(type, dialect);
		}
		int lenth = 0;
		if (lenstr != null)
			lenth = Integer.parseInt(lenstr);
		Column column = new Column(name.toUpperCase(), dbtype.toUpperCase(), lenth, notnull.equalsIgnoreCase("true") ? 0 : 1, defaultV,
				type.toUpperCase());
		column.setGenerator(generator);
		column.setSequence(sequence);
		column.setField(field);
		return column;
	}
	public TableVO getTableFromDB(final String name) throws Exception {
		Method m = this.getDataSource().getClass().getMethod("getUsername", null);
		final String uname = (String) m.invoke(this.getDataSource(), null);
		return (TableVO) this.getJdbcTemplate().execute(new ConnectionCallback() {
			public Object doInConnection(Connection c) throws SQLException, DataAccessException {
				DatabaseMetaData meta = c.getMetaData();
				String schema;
				if (uname == null) {
					schema = "%";
				} else {
					schema = uname.toUpperCase();
				}
				ResultSet table = meta.getColumns(c.getCatalog(), schema, name.toUpperCase(), "%");
				TableVO result = new TableVO();
				Map<String, Column> cols = new LinkedHashMap<String, Column>();
				while (table.next()) {
					String columnName = table.getString("COLUMN_NAME").toUpperCase();
					String columnType = table.getString("TYPE_NAME").toUpperCase();
					int datasize = table.getInt("COLUMN_SIZE");
					int nullable = table.getInt("NULLABLE");
					String defaultV = table.getString("COLUMN_DEF");
					Column col = new Column(columnName, columnType, datasize, nullable, defaultV);
					cols.put(col.getName(), col);
				}
				result.setColumns(cols);
				if (cols.size() > 0) {
					ResultSet pkrs = meta.getPrimaryKeys(c.getCatalog(), schema, name.toUpperCase());
					List<String> pk = new ArrayList<String>();
					while (pkrs.next()) {
						String columnName = pkrs.getString("COLUMN_NAME");
						String pkname = pkrs.getString("PK_NAME");
						pk.add(columnName);
						result.setPkname(pkname);
					}
					result.setPk(pk);
				}
				result.setName(name);
				return result;
			}
		});
	}
	public String getSeqName(final String seq) throws Exception {
		Method m = this.getDataSource().getClass().getMethod("getUsername", null);
		final String uname = (String) m.invoke(this.getDataSource(), null);
		return (String) this.getJdbcTemplate().execute(new ConnectionCallback() {
			@Override
			public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
				DatabaseMetaData dbmd = connection.getMetaData();
				String schema;
				if (uname == null) {
					schema = "%";
				} else {
					schema = uname.toUpperCase();
				}
				ResultSet colRet = dbmd.getTables(connection.getCatalog(), schema, seq.toUpperCase(), new String[]{"SEQUENCE"});
				String seqName = null;
				while (colRet.next()) {
					seqName = colRet.getString("TABLE_NAME");
				}
				return seqName;
			}
		});
	}
	public void AlterSeq(String seq) throws Exception {
		String before = this.getSeqName(seq);
		if (before == null) {
			createSeq(seq);
		}
	}
	public void AlterTable(TableVO table) {
		TableVO before = null;
		try {
			before = this.getTableFromDB(table.getName());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (before == null || before.getColumns().size() == 0) {
			String execute = table.createSql();
			log.info(execute);
			this.getJdbcTemplate().execute(execute);
		} else {
			for (Entry<String, Column> en : table.getColumns().entrySet()) {
				Column cbefore = before.getColumns().get(en.getKey().toUpperCase());
				if (cbefore == null) {
					String excution = add(table.getName(), en.getValue());
					log.info(excution);
					this.getJdbcTemplate().execute(excution);
				} else if (!cbefore.equals(en.getValue())) {
					if (table.getMutable()) {
						try {
							String excution = modify(table.getName(), en.getValue());
							log.info(excution);
							this.getJdbcTemplate().execute(excution);
						} catch (DataAccessException e) {
							log.error("更新列失败:" + e.getLocalizedMessage());
							String excution = drop(table.getName(), en.getValue());
							log.info(excution);
							this.getJdbcTemplate().execute(excution);
							excution = add(table.getName(), en.getValue());
							log.info(excution);
							this.getJdbcTemplate().execute(excution);
						}
					} else {
						try {
							String excution = modify(table.getName(), en.getValue());
							log.info(excution);
							this.getJdbcTemplate().execute(excution);
						} catch (DataAccessException e) {
							log.error("更新列失败:" + e.getLocalizedMessage());
							String excution = back(table.getName(), en.getValue());
							log.info(excution);
							this.getJdbcTemplate().execute(excution);
							excution = add(table.getName(), en.getValue());
							log.info(excution);
							this.getJdbcTemplate().execute(excution);
						}
					}
				}
			}
			boolean pkupdate = pkcompare(before.getPk(), table.getPk());
			if (!pkupdate) {
				if (before.getPk().size() > 0) {
					String excution = dropPk(table.getName(), before.getPkname());
					log.info(excution);
					this.getJdbcTemplate().execute(excution);
				}
				if (table.getPk().size() > 0) {
					String excution = addPk(table.getName(), table.getPkname(), table.getPk());
					log.info(excution);
					this.getJdbcTemplate().execute(excution);
				}
			}
		}
	}
	public void createSeq(String seqName) {
		String sql = "create sequence " + seqName;
		log.info(sql);
		this.getJdbcTemplate().execute(sql);
	}
	public boolean pkcompare(List<String> before, List<String> now) {
		if (before.size() != now.size()) {
			return false;
		}
		int modcount = before.size();
		for (String s : before) {
			for (String s1 : now) {
				if (s.equalsIgnoreCase(s1)) {
					modcount--;
					break;
				}
			}
		}
		if (modcount == 0) {
			return true;
		} else
			return false;
	}
	public String dropPk(String tab, String pkname) {
		// alter table 表名 drop constraint 主键名
		if (dialect.equalsIgnoreCase("Mysql")) {
			return "Alter table " + tab + " drop primary key";
		} else
			return "Alter table " + tab + " drop constraint " + pkname;
	}
	public String addPk(String tab, String pkname, List<String> pklist) {
		// alter table 表名 add constraint 主键名 primary key (column1,column2,....,column)
		StringBuffer sb = new StringBuffer();
		if (pklist.size() > 0) {
			for (String s : pklist) {
				sb.append(s + ",");
			}
			sb.deleteCharAt(sb.length() - 1);
		}
		if (dialect.equalsIgnoreCase("Mysql")) {
			return "Alter table " + tab + " add  primary key (" + sb.toString() + ")";
		} else {
			String temp = pkname == null ? "" : "constraint " + pkname;
			return "Alter table " + tab + " add  " + temp + " primary key (" + sb.toString() + ")";
		}
	}
	public String back(String tab, Column col) {
		return "Alter table " + tab + " rename column " + col.getName() + " to " + col.getName() + "_bak";
	}
	public String modify(String tab, Column col) {
		return "Alter table " + tab + " modify " + col.toString();
	}
	public String drop(String tab, Column col) {
		return "Alter table " + tab + " drop column " + col.getName();
	}
	public String add(String tab, Column col) {
		return "Alter table " + tab + " add " + col.toString();
	}
	public static void printlen(Object o, int len) {
		String s = String.valueOf(o);
		if (s.length() < len) {
			System.out.print(s);
			for (int i = 0; i < len - s.length(); i++) {
				System.out.print(" ");
			}
		}
	}
	public static String tableToXml(TableVO table) {
		StringBuffer result = new StringBuffer();
		result.append("<table name=\"" + table.getName() + "\" ");
		if (table.getClassname() != null) {
			result.append("class=\"" + table.getClassname() + "\" ");
		}
		result.append("mutable=\"" + table.getMutable() + "\">\r\n");
		if (table.getPk().size() > 0) {
			result.append("<id ");
			if (table.getPkname() != null) {
				result.append("name=\"" + table.getPkname() + "\" ");
			}
			result.append(">");
			for (String str : table.getPk()) {
				String col = columntoXml(table.getColumns().get(str));
				result.append(col);
				table.getColumns().remove(str);
			}
			result.append("</id>\r\n");
		}
		for (Entry<String, Column> en : table.getColumns().entrySet()) {
			String col = columntoXml(en.getValue());
			result.append(col);
		}
		result.append("</table>");
		return result.toString();
	}
	public static String columntoXml(Column column) {
		StringBuffer result = new StringBuffer();
		result.append("<column ");
		if (column.getName() != null) {
			result.append("name=\"" + column.getName() + "\" ");
		}
		if (column.getField() != null) {
			result.append("field=\"" + column.getField() + "\" ");
		}
		if (column.getDbtype() != null) {
			result.append("dbtype=\"" + column.getDbtype() + "\" ");
		}
		if (column.getType() != null) {
			result.append("type=\"" + column.getType() + "\" ");
		} else {
			result.append("type=\"" + SqlType.getType(column.getDbtype(), dialect) + "\" ");
		}
		if (column.getLenth() != 0) {
			result.append("length=\"" + column.getLenth() + "\" ");
		}
		if (column.getNullable() == 0) {
			result.append("not-null=\"true\" ");
		}
		if (column.getDefaultV() != null) {
			result.append("default=\"" + column.getDefaultV() + "\" ");
		}
		result.append("></column>\r\n");
		return result.toString();
	}
}
